﻿using System;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using GeneralLibs;
using Lists;

namespace KhuyenMai
{
    public class ChiTietKhuyenMaiTheoTongTienTichLuy : DoiTuongKhongCoCotID
    {
        #region ChuongTrinhKhuyenMai property
        public ChuongTrinhKhuyenMai ChuongTrinhKhuyenMai
        {
            get
            {
                if (_DataRow["ChuongTrinhKhuyenMaiID"] is Guid)
                    return DanhMucChuongTrinhKhuyenMai.Instance.GetByID((Guid)_DataRow["ChuongTrinhKhuyenMaiID"]);
                else
                    return null;
            }
            set
            {
                if (value is ChuongTrinhKhuyenMai)
                    _DataRow["ChuongTrinhKhuyenMaiID"] = value.ID;
                else
                    _DataRow["ChuongTrinhKhuyenMaiID"] = DBNull.Value;
            }
        }
        #endregion

        #region TongTienTu property
        private decimal _TongTienTu;
        public decimal TongTienTu
        {
            get
            {
                return (decimal)_DataRow["TongTienTu"];
            }
            set
            {
                _DataRow["TongTienTu"] = value;
            }
        }
        #endregion

        #region TongTienDen property
        private decimal _TongTienDen;
        public decimal TongTienDen
        {
            get
            {
                return (decimal)_DataRow["TongTienDen"];
            }
            set
            {
                _DataRow["TongTienDen"] = value;
            }
        }
        #endregion

        #region ChietKhauKhuyenMai property
        private decimal _ChietKhauKhuyenMai;
        public decimal ChietKhauKhuyenMai
        {
            get
            {
                return (decimal)_DataRow["ChietKhauKhuyenMai"];
            }
            set
            {
                _DataRow["ChietKhauKhuyenMai"] = value;
            }
        }
        #endregion

        public override void UpdateToDB()
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                UpdateToDB(cmd);
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(GeneralLibs.CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
            finally
            {
                con.Close();
            }
        }
        public override void UpdateToDB(SqlCommand cmd)
        {
            if (cmd.Connection.State != ConnectionState.Open) throw new CustomException("Chưa mở connection");
            string values = "";
            cmd.Parameters.Clear();
            foreach (DataColumn column in this.DataRow.Table.Columns)
            {
                values += string.Format("{0} = @{0}, ", column.ColumnName);
                if (this.DataRow[column.ColumnName] is Guid && (Guid)this.DataRow[column.ColumnName] == Guid.Empty)
                {
                    cmd.Parameters.AddWithValue(column.ColumnName, DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue(column.ColumnName, this.DataRow[column.ColumnName]);
                }
            }

            decimal a = (decimal)this.DataRow["ChietKhauKhuyenMai"];
            if (values.EndsWith(", ")) values = values.Substring(0, values.Length - 2);

            cmd.CommandText = string.Format("UPDATE ChiTietKhuyenMaiTheoTongTienTichLuy SET {0} WHERE ChuongTrinhKhuyenMaiID = '{1}' AND TongTienTu = {2} AND TongTienDen = {3}",
                                                values,
                                                this.DataRow["ChuongTrinhKhuyenMaiID"],
                                                this.DataRow["TongTienTu"],
                                                this.DataRow["TongTienDen"]);
            cmd.ExecuteNonQuery();
        }
        public override void DeleteFromDB()
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("DELETE FROM ChiTietKhuyenMaiTheoTongTienTichLuy WHERE ChuongTrinhKhuyenMaiID = '{0}' AND TongTienTu = {2} AND TongTienDen = {3}",
                                                    this.DataRow["ChuongTrinhKhuyenMaiID"],
                                                    this.DataRow["TongTienTu"],
                                                    this.DataRow["TongTienDen"]);
                cmd.ExecuteNonQuery();
                _DataRow.Delete();
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(GeneralLibs.CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
            finally
            {
                con.Close();
            }
        }

        public ChiTietKhuyenMaiTheoTongTienTichLuy(DataRow dataRow)
            : base(dataRow)
        {
        }
    }

    public class DanhMucChiTietKhuyenMaiTheoTongTienTichLuy : DanhMucDoiTuongKhongCoCotID, IEnumerable
    {
        #region Instance property: Singleton Design pattern
        private static DanhMucChiTietKhuyenMaiTheoTongTienTichLuy _Instance;
        public static DanhMucChiTietKhuyenMaiTheoTongTienTichLuy Instance
        {
            get
            {
                if (_Instance == null)
                {
                    _Instance = new DanhMucChiTietKhuyenMaiTheoTongTienTichLuy();
                    _Instance.DataTable.DefaultView.Sort = "TongTienTu";
                    _Instance.DataView = _Instance.DataTable.DefaultView;
                }
                return _Instance;
            }
        }
        #endregion

        #region override TableName property
        public override string TableName
        {
            get
            {
                return "ChiTietKhuyenMaiTheoTongTienTichLuy";
            }
        }
        #endregion

        #region Methods required by IEnumerable (use for foreach)
        public IEnumerator GetEnumerator()
        {
            return new TokenEnumerator(this);
        }

        private class TokenEnumerator : IEnumerator
        {
            private int _Position = -1;
            DanhMucDoiTuongKhongCoCotID _Dstk;

            public TokenEnumerator(DanhMucDoiTuongKhongCoCotID dstk)
            {
                _Dstk = dstk;
            }

            // Declare the MoveNext method required by IEnumerator:
            public bool MoveNext()
            {
                if (_Position < _Dstk.DataView.Count - 1)
                {
                    _Position++;
                    return true;
                }
                else
                {
                    return false;
                }
            }

            // Declare the Reset method required by IEnumerator:
            public void Reset()
            {
                _Position = -1;
            }

            // Declare the Current property required by IEnumerator:
            public object Current
            {
                get
                {
                    return new ChiTietKhuyenMaiTheoTongTienTichLuy((_Dstk.DataView[_Position].Row));
                }
            }
        }
        #endregion

        public ChiTietKhuyenMaiTheoTongTienTichLuy this[int index]
        {
            get
            {
                if (0 <= index && index <= this.Count)
                {
                    return new ChiTietKhuyenMaiTheoTongTienTichLuy(DataTable.Rows[index]);
                }
                else
                {
                    return null;
                }
            }
        }

        public ChiTietKhuyenMaiTheoTongTienTichLuy NewDoiTuong()
        {
            return new ChiTietKhuyenMaiTheoTongTienTichLuy(DataTable.NewRow());
        }

        public ChiTietKhuyenMaiTheoTongTienTichLuy GetByProperty(string property, object value)
        {
            try
            {
                DataRow[] rows = GetDataRows(property, value);
                if (rows.Length > 0) return new ChiTietKhuyenMaiTheoTongTienTichLuy(rows[0]);
                else return null;
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public ChiTietKhuyenMaiTheoTongTienTichLuy GetByKey(Guid chuongTrinhKhuyenMaiID, int tongTienTu, int tongTienDen)
        {
            try
            {
                string filterExpression = string.Format("ChuongTrinhKhuyenMaiID = {0} AND TongTienTu = {1} AND AND TongTienDen = {2}", chuongTrinhKhuyenMaiID, tongTienTu, tongTienDen);
                DataRow[] rows = GetDataRows(filterExpression);
                if (rows.Length > 0) return new ChiTietKhuyenMaiTheoTongTienTichLuy(rows[0]);
                else return null;
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        
        public static DanhMucChiTietKhuyenMaiTheoTongTienTichLuy GetAll()
        {
            return Instance;
        }

        public static DanhMucChiTietKhuyenMaiTheoTongTienTichLuy Filter(string filterExpression)
        {
            try
            {
                DanhMucChiTietKhuyenMaiTheoTongTienTichLuy obj = new DanhMucChiTietKhuyenMaiTheoTongTienTichLuy();
                obj.DataView = new DataView(DanhMucChiTietKhuyenMaiTheoTongTienTichLuy.Instance.DataTable, filterExpression, "", DataViewRowState.CurrentRows);
                return obj;
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public ChiTietKhuyenMaiTheoTongTienTichLuy GetByIDs(Guid chuongTrinhKhuyenMaiID, decimal tongTienTu, decimal tongTienDen)
        {
            try
            {
                string filterExpression = string.Format("ChuongTrinhKhuyenMaiID = '{0}' AND TongTienTu = '{1}' AND TongTienDen = '{2}'", chuongTrinhKhuyenMaiID, tongTienTu, tongTienDen);
                DataRow[] rows = GetDataRows(filterExpression);
                if (rows.Length > 0) return new ChiTietKhuyenMaiTheoTongTienTichLuy(rows[0]);
                else return null;
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        public DanhMucChiTietKhuyenMaiTheoTongTienTichLuy GetByChuongTrinhKhuyenMaiID(Guid chuongTrinhKhuyenMaiID)
        {
            try
            {
                string filterExpression = string.Format("ChuongTrinhKhuyenMaiID = '{0}'", chuongTrinhKhuyenMaiID);
                return Filter(filterExpression);
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public void DeleteChiTietKhuyenMaiTheoTongTienTichLuyByChuongTrinhKhuyenMaiIDFromDB(Guid chuongTrinhKhuyenMaiID)
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                DeleteChiTietKhuyenMaiTheoTongTienTichLuyByChuongTrinhKhuyenMaiIDFromDB(cmd, chuongTrinhKhuyenMaiID);
                base.LoadFromDB();
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(GeneralLibs.CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
            finally
            {
                con.Close();
            }
        }
        public void DeleteChiTietKhuyenMaiTheoTongTienTichLuyByChuongTrinhKhuyenMaiIDFromDB(SqlCommand cmd, Guid chuongTrinhKhuyenMaiID)
        {
            try
            {
                cmd.CommandText = string.Format("DELETE FROM ChiTietKhuyenMaiTheoTongTienTichLuy WHERE ChuongTrinhKhuyenMaiID = '{0}'",
                                                    chuongTrinhKhuyenMaiID);
                cmd.ExecuteNonQuery();
                DataRow[] rows = DataTable.Select(string.Format("ChuongTrinhKhuyenMaiID = '{0}'", chuongTrinhKhuyenMaiID));
                foreach (DataRow r in rows)
                {
                    DataTable.Rows.Remove(r);
                }
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(GeneralLibs.CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public decimal CalculateTongTienTichLuyTheoKhachHang(Guid doiTuongNghiepVuID, DateTime? ngayBatDauTinh, DateTime? ngayKetThucTinh)
        {
            decimal sumTongTienTichLuy = 0;
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                CalculateTongTienTichLuyTheoKhachHang(cmd, doiTuongNghiepVuID, ngayBatDauTinh, ngayKetThucTinh, ref sumTongTienTichLuy);
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(GeneralLibs.CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
            finally
            {
                con.Close();
            }
            return sumTongTienTichLuy;
        }
        public void CalculateTongTienTichLuyTheoKhachHang(SqlCommand cmd, Guid doiTuongNghiepVuID, DateTime? ngayBatDauTinh, DateTime? ngayKetThucTinh, ref decimal sumTongTienTichLuy)
        {
            cmd.CommandText = string.Format("SELECT SUM(ThanhTienQD) TongTien FROM NhatKyXuatKho  \n" +
                                            "WHERE DoiTuongNghiepVu1ID = '{0}' AND @NgayBatDauTinh <= Ngay AND Ngay <= @NgayKetThucTinh"
                                            , doiTuongNghiepVuID);
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@NgayBatDauTinh", ngayBatDauTinh);
            cmd.Parameters.Add("@NgayKetThucTinh", ngayKetThucTinh);
            SqlDataReader dr = cmd.ExecuteReader();
            
            try
            {
                if (dr.Read())
                {
                    sumTongTienTichLuy = Convert.ToDecimal(dr["TongTien"]);
                }
            }
            catch (Exception ex)
            {
                BasicForms.FrmWarning.Show("Sự cố", string.Format("Không tính được tổng tiền của khách hàng '{0}' trong khoảng thời gian {1} đến {2}",
                    DanhMucDoiTuongNghiepVu.Instance.GetByID(doiTuongNghiepVuID).Ten, ngayBatDauTinh, ngayKetThucTinh));
            }

        }
    }
}
